등록일:2009-05-28 00:30:17 (0%) 작성자: 제목:Nested Loop Join 성능향상과 관련된 2가지 원리
한 선지자에 의하여 9i/10g 그리고 11g 에서 Nested Loop Join 수행시 buffer pinning 효과에 의한 성능향상이 증명된바 있다. 물론 그글은 명불허전 이다.(해당글 링크:http://ukja.tistory.com/166) 이미 밝혀진 원리에 대해서 증명이나 검증하는것은 더이상 과학이 아니다. 그리고 재미도 없다. 따라서 오늘은 buffer pinning 에 의한 성능개선이 아닌 또다른 성능개선에 대한 2가지 원리 대해서 연구해보자.
이제부터 버젼별로 변경된 Nested Loop Join 의 실행계획과 그에 따르는 원리에 대하여 알아보려 한다. 모든 예제는 오라클 설치시 자동으로 설치되는 SH Schema의 customers 테이블과 sales 테이블을 이용한다. 먼저 인덱스를 하나 만든다.
drop index sh.sales_cust_ix; create index sh.sales_cust_ix on sh.sales(cust_id);
이어서 buffer cache 를 비우고 SQL 을 실행한다. 고객 테이블을 full scan 하고 sales 테이블과 Nested Loop Join 을 수행한다.
alter system flush buffer_cache;
select /*+ gather_plan_statistics */ count(*) from (select /*+ no_merge full(c) use_nl(c s) */ s.cust_id, s.time_id, c.cust_year_of_birth from sh.customers c, sh.sales s where c.cust_id = s.cust_id and c.cust_year_of_birth between 1960 and 1980 and s.channel_id between 2 and 3 and s.prod_id < 18);
select * from table(dbms_xplan.display_cursor(null,null, 'allstats last -rows +outline -predicate' ));
Oracle 8i Plan --> just normal ------------------------------------------------ | Id | Operation | ---------------------------------------------- | | 1 | SORT AGGREGATE | | 2 | VIEW | | 3 | NESTED LOOPS | | 4 | TABLE ACCESS FULL | | 5 TABLE ACCESS BY GLOBAL INDEX ROWID| | 6 | INDEX RANGE SCAN | ------------------------------------------------
특별 할것 없는 전통적인 Nested Loop Join 이다. 이제 9i 및 10g 의 plan 을 보자.
Oracle 9i 에서 table prefetch 기능이 나오다. Inner(후행) 테이블의 위치가 Nested Loop Join 위로 올라가 버렸다. 그리고 오라클이 내부적으로 NLJ_PREFETCH 힌트를 사용하였다. 이것은 어떤 의미를 가지고 있을까? 이러한 현상에 대한 원리는 single block I/O request 에 의한 physical read 시 block 을 prefetch(미리 읽는 작업) 한다는데 있다. 여기서 physical read 란 buffer cache 에 데이터가 없어서 disk 에서 데이터를 read 하는것을 의미한다. 어차피 scan할 data 이므로 미리 엑세스할 물리적 주소를 여러개(운반단위) 모은다음 한번에 read 햐여 buffer cache 에 올리게 되는것이다. 여기서 주의할점은 multi block I/O 를 하는것이 아니라 single block I/O 여러개(운반단위만큼)가 동시에 진행된다는 것이다. 이것을 Vector IO 라고 부른다.(Batch IO 라고도 함) 바로 여기에 성능개선 효과가 있는것이다. 이기능에 의해서 rowid 에 의한 테이블 access 는 8i 에 비해서 상당한 개선 효과가 있는 것이다.(Operation ID 로는 3번이 여기 해당된다.)
이것을 증명하기 위해 v$sesstat 에서 SQL 수행전과 수행후의 value 증가분을 비교해보면 아래와 같다.
위에서 보는것과 같이 table prefetch 가 발생하였다. 위의 테스트는 11g 에서 수행된것인데 9i 의 살행계획과 실행통계도 10g 와 대동소이 하다. 11g 에서 이전 버젼(9i/10g) 번젼의 plan 을 나타나게 하려면 NO_NLJ_BATCHING(테이블명) 힌트를 사용하면 된다. 9i 나 10g 에서의 후행 테이블 prefetch에 의한 성능 개선효과는 11g 에 와서야 완벽한 모습을 갖추게 된다.
궁하면 통한다. 이상하지 않은가? 테이블이 2개 인데 Nested Loop Join 이 하나가 아닌 2개가 되어버렸다. 또한 NLJ_PREFETCH 힌트가 사라지고 NLJ_BATCHING 힌트로 대체 되었다. 이러한 현상이 의미하는 바는 무엇일까? 9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다. 드디어 11g 에서 index scan 시 Vector IO 가 가능해졌다. 궁하면 통한다고 했던가? 오라클이 Nested Loop Join 에 대하여 지속적으로 개선해왔다는것을 알수있다. 참고로 NO_NLJ_BATCHING 힌트를 사용하면 9i/10g 의 Plan 으로 돌아가게 된다.
그러면 11g 의 버젼에서 v$sesstat 통계를 보자.
NAME DIFF ---------------------------------------- ---------- Batched IO vector block count 3758 Batched IO vector read count 50 ... 이후 생략
위에서 보듯이 Batched IO 란것이 생겼다. Batched IO (혹은 Vector IO) 기능에 힘입어 table prefetch 에 이어서 11g 에서는 index scan 의 성능까지 향상되었다.
주의사항 : 위에서 수행한 모든 테스트는 Physical read 시에만 해당된다. 위의 예제 스크립트에 buffer cache 를 flush 한 이유도 여기에 있다.
결론: 오라클 9i, 10g 및 11g 에서 개선된 Nested Loop Join 의 원리는 다음과 같다. 첫번째는 9I/10g 에서 후행 테이블의 TABLE ACCESS BY INDEX ROWID Operation의 작업속도가 개선되었다는것과 두번째로 11g 에서 후행 테이블의 인덱스 scan 속도까지 획기적으로 개선되었다는 것이다. 이것은 table prefetch 기능과 Vector I/O 기능에 의해서 각각 구현 되었다. 이기능들과 별도로 이글의 서두에서 이야기한 buffer pinning 기능까지 덤으로 따라오게 되었다.
앞으로 11g 를 사용시 과거처럼 Batch 용 SQL 에서 무조건 hash 조인을 남발하지 말았으면 한다. 조인건수가 많지 않고 후행 테이블에 적당한 인덱스가 있을 경우에 최소한 Nested Loop Join 과 성능비교를 해보아야 하지않을까?